﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data.Sql;

namespace Electure.DAL
{
    public class Publication: BaseModel
    {
        public string Name { get; set; }
        public DateTime CreateDate { get; set; }
        public DateTime EditDate { get; set; }
        public string Description { get; set; }
        public Category Category { get; set; }
        public ScienceDepartment ScienceDepartment { get; set; }
        public Attachment Image { get; set; }
        public List<Attachment> VideoFiles { get; set; }
        public Publication()
        {
            VideoFiles = new List<Attachment>();
        }

    }
    public class PublicationRepository : BaseRepository<Publication>
    {
        internal PublicationRepository() { }
        protected override int create(Publication instance)
        {
            string sql = "INSERT INTO publication ({0}) values ({1})",
                       fields = "", values = "";

            var param = new DynamicParameters();

            param.Add("Name", instance.Name);
            param.Add("Description", instance.Description);

            if (instance.Category != null)
                param.Add("CategoryId", instance.Category.ID);

            if (instance.Image != null)
                param.Add("ImageId", instance.Image.ID);

            if (instance.ScienceDepartment != null)
                param.Add("ScienceDepartmentId", instance.ScienceDepartment.ID);

            param.ParameterNames
                .ToList()
                .ForEach(item => { fields += item + ","; values += "@" + item + ","; });

            sql = string.Format(sql, fields.Substring(0, fields.Length - 1), values.Substring(0, values.Length - 1));
            return connection.Execute(sql, param);
        }
        public override int Create(Publication instance)
        {
            try
            {
                if (create(instance) != 1)
                    return -1;

                int newId = (int)connection.Query("SELECT @@IDENTITY newid").FirstOrDefault().newid;

                addVideoFiles(instance.VideoFiles, newId);

                return newId;
            }
            catch (Exception e)
            {
                return -1;
            }
        }
        public override bool Update(Publication instance)
        {
            string sql = "UPDATE publication SET {0} editdate = getutcdate() WHERE id = @ID",
                fields = "";

            var param = new DynamicParameters();

            param.Add("Name", instance.Name);
            param.Add("Description", instance.Description);

            if (instance.Category != null && instance.Category.ID != -1)
                param.Add("CategoryId", instance.Category.ID);
            else if (instance.Category != null && instance.Category.ID == -1)
              param.Add("CategoryId", DBNull.Value);

            if (instance.Image != null && instance.Image.ID != -1)
                param.Add("ImageId", instance.Image.ID);
            else if (instance.Image != null && instance.Image.ID == -1)
                param.Add("ImageId", DBNull.Value);

            if (instance.ScienceDepartment != null && instance.ScienceDepartment.ID != -1)
                param.Add("ScienceDepartmentId", instance.ScienceDepartment.ID);
            else if (instance.ScienceDepartment != null && instance.ScienceDepartment.ID == -1)
                param.Add("ScienceDepartmentId", DBNull.Value);

            param.ParameterNames
                .ToList()
                .ForEach(item => { fields += string.Format("{0} = @{0},", item); });

            sql = string.Format(sql, fields);

            addVideoFiles(instance.VideoFiles, instance.ID);
            param.Add("ID", instance.ID);
            return connection.Execute(sql, param) == 1;

        }
        public override bool Delete(int id)
        {
            return connection.Execute("DELETE FROM publication WHERE ID = @id", new { id = id }) == 1;
        }
        public override List<Publication> GetAll()
        {
            return GetAll();
        }
        public List<Publication> GetAll(int categoryId = -1, int depId = -1)
        {
            return GetAll(-1, categoryId, depId);
        }
        private List<Publication> GetAll(int publicationId, int categoryId, int depId)
        {
            try
            {
                string sql = @"SELECT * FROM Publication p
                    LEFT JOIN Attachment a ON a.ID = p.ImageId
                    LEFT JOIN Category c ON c.ID = p.CategoryId
                    LEFT JOIN ScienceDepartment sd ON sd.ID = p.ScienceDepartmentId",                    
                    whereCondition = "",
                    orderCondition = " ORDER BY p.ID";

                var param = new DynamicParameters();
                if (publicationId != -1)
                    param.Add("ID", publicationId);
                if (categoryId != -1)
                    param.Add("CategoryId", categoryId);
                if (depId != -1)
                    param.Add("ScienceDepartmentId", depId);

                param.ParameterNames
                    .ToList()
                    .ForEach(item => { whereCondition += string.Format(" p.{0} = @{0} AND", item); });

                if (!string.IsNullOrEmpty(whereCondition))
                    sql = sql + " WHERE " + whereCondition.Substring(0, whereCondition.Length - 4);


                sql += orderCondition;

                var result = connection.Query<Publication, Attachment, Category, ScienceDepartment, Publication>(
                    sql,
                    (publication, attachment, category, scienceDepartment) =>
                    {
                        publication.Image = attachment;
                        publication.Category = category;
                        publication.ScienceDepartment = scienceDepartment;
                        return publication;
                    }, param).ToList();

                result.ForEach(item => item.VideoFiles = getPulicationVideoFiles(item.ID));

                return result;
            }
            catch (Exception e)
            {
                return null;
            }
        }
        private List<Attachment> getPulicationVideoFiles(int id)
        {
            return connection.Query<Attachment>(
                        @"SELECT a.* FROM Attachment a
                            INNER JOIN PublicationWithVideo p ON a.ID = p.AttachmentId AND p.PublicationId = @id",
                        new { id = id }).ToList();
        }
        private bool addVideoFiles(List<Attachment> VideoFiles, int publicationId)
        {
            connection.Execute("DELETE FROM PublicationWithVideo WHERE PublicationId = @id", new { id = publicationId });
            
            if (VideoFiles.Count == 0)
                return true;

            var videoInstances = new object[VideoFiles.Count];
            int i = 0;            

            VideoFiles.ForEach(item => videoInstances[i++] = new { a = item.ID, p = publicationId });

            return connection.Execute("INSERT INTO PublicationWithVideo (PublicationId, AttachmentId) values(@p,@a)", videoInstances) != videoInstances.Length;
        }
        public override Publication GetById(int id)
        {
            return GetAll(id, -1, -1).SingleOrDefault();
        }
        public List<Publication> Search(string query)
        {
            List<KeyValuePair<int, Publication>> result = new List<KeyValuePair<int, Publication>>();
            var publications = GetAll();

            var queryList = query.ToLower().Split(new char[] { ' ' });

            publications.ForEach(item =>
            {
                int cnt = 0;

                cnt += item.Name.ToLower().Split(queryList, StringSplitOptions.None).Length - 1;
                cnt += item.Description.ToLower().Split(queryList, StringSplitOptions.None).Length - 1;

                if (cnt > 0)
                {
                    result.Add(new KeyValuePair<int, Publication>(cnt, item));
                }
            });

            return (from item in result
                    orderby item.Key descending
                    select item.Value)
                    .ToList();
        }
    }
}
 